package com.project;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.regex.*;

/**
 *
 */
public class DB_User extends DB_Operator {

    /**
     * Default constructor
     */
    public DB_User() {
        super();
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public boolean add(List<String> input) {
        if (input.size() != 6) {
            return false;
        }
        connect();
        String sql = "INSERT INTO `User` (`name`, `pid`, `email`, `phone`, `identity`, `password`) VALUES (?,?,?,?,?,?)";
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            PStmt.setString(1, input.get(0));
            PStmt.setString(2, input.get(1));
            PStmt.setString(3, input.get(2));
            PStmt.setString(4, input.get(3));
            PStmt.setInt(5, Integer.parseInt(input.get(4)));
            PStmt.setString(6, input.get(5));
            int rowsAffected = PStmt.executeUpdate();
            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public boolean delete(List<String> input) {
        if (input.isEmpty()) {
            return false;
        }
        connect();
        String deleteSql = "DELETE FROM `User` WHERE `uid` =?";
        String maxIdSql = "SELECT MAX(uid) FROM `User`";
        String updateAutoIncrementSql = "ALTER TABLE `User` AUTO_INCREMENT =?";
        try (PreparedStatement deletePStmt = con.prepareStatement(deleteSql);
             PreparedStatement maxIdPStmt = con.prepareStatement(maxIdSql);
             PreparedStatement updatePStmt = con.prepareStatement(updateAutoIncrementSql)) {
            deletePStmt.setString(1, input.getFirst());
            int rowsAffected = deletePStmt.executeUpdate();
    
            ResultSet rs = maxIdPStmt.executeQuery();
            int maxId = 1;
            if (rs.next()) {
                maxId = rs.getInt(1) + 1;
            }
            updatePStmt.setInt(1, maxId);
            updatePStmt.executeUpdate();
    
            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public List<String> find(List<String> input) {
        List<String> result = new ArrayList<>();
        connect();
        StringBuilder sql = new StringBuilder("SELECT uid, name, pid, email, phone, identity, is_registered, password FROM User WHERE 1=1");
        List<Object> params = new ArrayList<>();
    
        try {
            Set<String> validFields = Set.of(
                    "`uid`", "`name`", "`pid`", "`email`",
                    "`phone`", "`identity`", "`is_registered`", "`password`"
            );
            for (String condition : input) {
                Pattern pattern = Pattern.compile("(<=|>=|=|<|>)");
                Matcher matcher = pattern.matcher(condition);
                if (matcher.find()) {
                    String operator = matcher.group();
                    String[] parts = condition.split(Pattern.quote(operator), 2);
                    String field = parts[0].trim().toLowerCase();
                    String value = parts[1].trim();
    
                    if (!validFields.contains(field)) continue;
    
                    if (Set.of("`uid`", "`identity`", "`is_registered`").contains(field)) {
                        try {
                            params.add(Integer.parseInt(value));
                        } catch (NumberFormatException e) {
                            continue;
                        }
                    } else {
                        params.add(value);
                    }
                    sql.append(" AND ").append(field).append(operator).append("?");
                }
            }

            sql.append(" ORDER BY `uid`");
    
            try (PreparedStatement PStmt = con.prepareStatement(sql.toString())) {
                for (int i = 0; i < params.size(); i++) {
                    Object param = params.get(i);
                    if (param instanceof Integer) {
                        PStmt.setInt(i + 1, (Integer) param);
                    } else {
                        PStmt.setString(i + 1, param.toString());
                    }
                }
    
                // 处理结果集
                ResultSet rs = PStmt.executeQuery();
                while (rs.next()) {
                    String row = String.join(",",
                            rs.getString("uid"),
                            rs.getString("name"),
                            rs.getString("pid"),
                            rs.getString("email"),
                            rs.getString("phone"),
                            rs.getString("identity"),
                            rs.getString("is_registered"),
                            rs.getString("password")
                    );
                    result.add(row);
                }
            }
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
        } finally {
            close();
        }
        return result;
    }

    /**
     * @param conditionColumn 条件
     * @param conditionValue 值
     * @param returnList 返回
     */
    public void find(String conditionColumn, String conditionValue, List<String> returnList) {
        connect();
        String sql = "SELECT * FROM `User` WHERE ture";
        if (!conditionColumn.isEmpty() &&!conditionValue.isEmpty()) {
            sql += " AND `" + conditionColumn + "` =?";
        }
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            if (!conditionColumn.isEmpty() &&!conditionValue.isEmpty()) {
                PStmt.setString(1, conditionValue);
            }
            ResultSet rs = PStmt.executeQuery();
            while (rs.next()) {
                returnList.add(rs.getString("uid"));
                returnList.add(rs.getString("name"));
                returnList.add(rs.getString("pid"));
                returnList.add(rs.getString("email"));
                returnList.add(rs.getString("phone"));
                returnList.add(String.valueOf(rs.getInt("identity")));
                returnList.add(String.valueOf(rs.getInt("is_registered")));
                returnList.add(rs.getString("password"));
            }
            close();
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
        }
    }

    /**
     * @param input 输入
     * @return 返回
     */
    public boolean update(List<String> input) {
        if (input.size() != 8) {
            return false;
        }
        connect();
        String sql = "UPDATE `User` SET `name`=?, `pid`=?, `email`=?, `phone`=?, `identity`=?, `is_registered`=?," +
                " `password`=? WHERE `uid`=?";
        try (PreparedStatement PStmt = con.prepareStatement(sql)) {
            PStmt.setString(1, input.get(1));
            PStmt.setString(2, input.get(2));
            PStmt.setString(3, input.get(3));
            PStmt.setString(4, input.get(4));
            PStmt.setInt(5, Integer.parseInt(input.get(5)));
            PStmt.setInt(6, Integer.parseInt(input.get(6)));
            PStmt.setString(7, input.get(7));
            PStmt.setString(8, input.get(0));
            int rowsAffected = PStmt.executeUpdate();
            close();
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.err.println("出现异常：" + e.getMessage());
            close();
            return false;
        }
    }
}